
ALTER     proc change_order_limits_proc 

@totalOrder_limit float, @previousOrder_limit float,
@marketing_hold int, @finance_hold_string varchar(20),
@marketing_hold_string varchar(20),
@operations_hold_string varchar(20), 
@salesOrder_id int

as

declare @operations_hold int
declare @finance_hold int 
declare @changeOrder_limit float 
set @finance_hold = 0
set @operations_hold =0

	/*
	* Added by vasanth 09/2/2003
	* If atall therez a change then set the @operations_hold to 1 else no
	*/
if( @totalOrder_limit > (select Finance_Order_Limit FROM Sales_Order_Limits) or @changeOrder_limit > (select Finance_Change_Order_Limit FROM Sales_Order_Limits) )
	begin
		set @operations_hold = 1
	end



if(@totalOrder_limit > (select Finance_Order_Limit FROM Sales_Order_Limits))
begin
	set @finance_hold = 1
end


set @changeOrder_limit = @previousOrder_limit - @totalOrder_limit

if(@changeOrder_limit > (select Finance_Change_Order_Limit FROM Sales_Order_Limits))
begin
	set @finance_hold = 1
end

if(@finance_hold = 1)
begin
if not exists (select * from Sales_Order_Hold_Table 
	where SalesOrder_id = @salesOrder_id and Hold_Type = @finance_hold_string)

	begin
	insert into Sales_Order_Hold_Table(SalesOrder_Id, Hold_Type, User_Id, 
		Is_Approved) VALUES(@salesOrder_id, @finance_hold_string, 
		'SYSTEM', @finance_hold)
	end	
else
	begin
	Update Sales_Order_Hold_Table SET User_Id = 'SYSTEM', Is_approved = @finance_hold
		 where SalesOrder_Id = @salesOrder_id and Hold_Type = @finance_hold_string	
	end
end

if(@marketing_hold = 1)
begin
if not exists (select * from Sales_Order_Hold_Table 
		where SalesOrder_Id = @salesOrder_id and Hold_Type = @marketing_hold_string)
	begin
	insert into Sales_Order_Hold_Table(SalesOrder_Id, Hold_Type, User_Id, 
		Is_Approved) VALUES(@salesOrder_id, @marketing_hold_string, 
		'SYSTEM', @marketing_hold)
	end	
else
	begin
	Update Sales_Order_Hold_Table SET User_Id = 'SYSTEM', Is_approved = @finance_hold
		 where SalesOrder_Id = @salesOrder_id and Hold_Type = @finance_hold_string
	end
end

if(@operations_hold = 1)
begin
if not exists (select * from Sales_Order_Hold_Table 
		where SalesOrder_Id = @salesOrder_id and Hold_Type = @operations_hold_string)
	begin
	insert into Sales_Order_Hold_Table(SalesOrder_Id, Hold_Type, User_Id, 
		Is_Approved) VALUES(@salesOrder_id, @operations_hold_string, 
		'SYSTEM', @operations_hold)
	end	
else
	begin
	Update Sales_Order_Hold_Table SET User_Id = 'SYSTEM', Is_approved = @operations_hold
		 where SalesOrder_Id = @salesOrder_id and Hold_Type = @finance_hold_string
	end
end

if(@finance_hold = 1 or @marketing_hold = 1 or @operations_hold = 1)
begin
	UPDATE Sales_Order SET SO_Is_Pending = 0, TimeStamp = CURRENT_TIMESTAMP
	WHERE SalesOrder_Id = @salesOrder_id
end	

